#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "wangjing"
# Date: 2018/10/16
#根据气象规则，获取温馨提示----规则和条件的筛选

def Obtain_sql(sql_pre, sql_str):
    '''

    :param sql_pre: 数据库语句
    :param sql_str: 把查询的数据转变为datafreme的columns
    :return: 查询数据库，转化为datafrme
    '''
    server = "119.29.146.108"
    user = "sa"
    password = "S3686-e342qq-3686e342"

    connect = pymssql.connect(server, user, password, "istrong_data_collection", charset="utf8")  # 获取连接
    cursor = connect.cursor()  # 获取光标

    cursor.execute(sql_pre)
    number_pre = cursor.fetchall()
    columns = [x for x in sql_str.split(',')]
    weater_data_pre = pd.DataFrame(number_pre, columns=columns)

    connect.close()
    return weater_data_pre


def tm():
    # 初始化LJ_TM的数据
    sql_str_weater = 'code'
    sql_pre_weater = "SELECT   {} from {}".format(sql_str_weater, 'LJ_WeaterStation')
    a = Obtain_sql(sql_pre_weater, sql_str_weater)['code'].tolist()

    server = "119.29.146.108"
    user = "sa"
    password = "S3686-e342qq-3686e342"

    connect = pymssql.connect(server, user, password, "istrong_data_collection", charset="utf8")  # 获取连接
    cursor = connect.cursor()  # 获取光标

    for x in a:
        print(x)

        cursor.execute(
            "INSERT INTO LJ_TM(code) VALUES ({})".format(x)

        )

        # 你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true
        connect.commit()
    connect.close()

    print(a)

def test():
    # 初始化LJ_TM的数据
    a= [('未来7天，以多云天气为主，昼夜温差较大','7'),
        ('未来15天，有大幅度降温','15'),
        ('未来40天，以多云天气为主，有大幅度降温','40')]

    server = "119.29.146.108"
    user = "sa"
    password = "S3686-e342qq-3686e342"

    connect = pymssql.connect(server, user, password, "istrong_data_collection", charset="utf8")  # 获取连接
    cursor = connect.cursor()  # 获取光标

    for x in a:
        print(x)
        print("INSERT INTO LJ_Tips(code,time,tips,rqsj) VALUES (10123010101,{},'{}','2018-10-18')".format(x[1],x[0]))
        cursor.execute(
            "INSERT INTO LJ_Tips(code,time,tips,rqsj) VALUES (10123010101,{},'{}','2018-10-18')".format(x[1],x[0])

        )

        # 你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true
        connect.commit()
    connect.close()

import pandas as pd
def my_min(a,b):
    if a >0:
        return b


if __name__ == '__main__':
    # s = pd.Series(
    #     [10.0247, 10.0470, 10.0647, 10.0761, 15.0800, 10.0761, 10.0647, 10.0470, 10.0247, 10.0, 9.9753, 9.9530, 9.9353,
    #      9.9239, 18.92, 9.9239, 9.9353, 9.9530, 9.9753, 10.0])
    # df = pd.DataFrame(s)
    # df.columns = ['value']
    # df['val_1'] = df['value'].diff()
    # df['val_2'] = df['val_1'].shift(-1)
    # b = df.apply(lambda row:row['val_2'] if int(row['val_1']) >= 0 else 0 , axis=1)
    # a = df.apply(lambda row: my_min(row['val_1'],row['val_2']), axis=1)
    # print(10*'*')
    # print(df['val_1'] )
    # print(10 * '*')
    # print(df['val_2'])
    # print(10 * '*')
    # print(df['val'])
    import random

    a = ['1', '2', '3', '4', '5', '6', '7', '8', '9']
    b = str(random.sample(a, 1)).replace('[','').replace(']','')
    print(type(b))
    print(b)